package com.rickpan.repository;

import com.rickpan.entity.GeneratedReport;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * AI生成报告数据访问层
 */
@Repository
public interface GeneratedReportRepository extends JpaRepository<GeneratedReport, Long>, 
                                                  JpaSpecificationExecutor<GeneratedReport> {

    /**
     * 根据用户ID查询报告列表（分页）
     */
    Page<GeneratedReport> findByUserIdOrderByCreatedAtDesc(Long userId, Pageable pageable);

    /**
     * 根据用户ID和报告类型查询报告列表
     */
    Page<GeneratedReport> findByUserIdAndTypeOrderByCreatedAtDesc(Long userId, 
                                                                 GeneratedReport.ReportType type, 
                                                                 Pageable pageable);

    /**
     * 根据用户ID和状态查询报告列表
     */
    Page<GeneratedReport> findByUserIdAndStatusOrderByCreatedAtDesc(Long userId, 
                                                                   GeneratedReport.ReportStatus status, 
                                                                   Pageable pageable);

    /**
     * 根据用户ID、类型和状态查询报告列表
     */
    Page<GeneratedReport> findByUserIdAndTypeAndStatusOrderByCreatedAtDesc(Long userId, 
                                                                          GeneratedReport.ReportType type,
                                                                          GeneratedReport.ReportStatus status, 
                                                                          Pageable pageable);

    /**
     * 根据用户ID和标题关键词搜索报告
     */
    @Query("SELECT r FROM GeneratedReport r WHERE r.userId = :userId " +
           "AND (r.title LIKE %:keyword% OR r.originalContent LIKE %:keyword% OR r.editedContent LIKE %:keyword%) " +
           "ORDER BY r.createdAt DESC")
    Page<GeneratedReport> findByUserIdAndKeyword(@Param("userId") Long userId, 
                                                @Param("keyword") String keyword, 
                                                Pageable pageable);

    /**
     * 根据用户ID和时间范围查询报告
     */
    Page<GeneratedReport> findByUserIdAndCreatedAtBetweenOrderByCreatedAtDesc(Long userId, 
                                                                             LocalDateTime startTime, 
                                                                             LocalDateTime endTime, 
                                                                             Pageable pageable);

    /**
     * 根据用户ID和报告时间范围查询报告
     */
    Page<GeneratedReport> findByUserIdAndPeriodStartBetweenOrderByCreatedAtDesc(Long userId, 
                                                                               LocalDate startDate, 
                                                                               LocalDate endDate, 
                                                                               Pageable pageable);

    /**
     * 检查用户是否已存在相同时间范围和类型的报告
     */
    Optional<GeneratedReport> findByUserIdAndTypeAndPeriodStartAndPeriodEnd(Long userId, 
                                                                           GeneratedReport.ReportType type,
                                                                           LocalDate periodStart, 
                                                                           LocalDate periodEnd);

    /**
     * 统计用户报告总数
     */
    long countByUserId(Long userId);

    /**
     * 统计用户各类型报告数量
     */
    @Query("SELECT r.type, COUNT(r) FROM GeneratedReport r WHERE r.userId = :userId GROUP BY r.type")
    List<Object[]> countByUserIdGroupByType(@Param("userId") Long userId);

    /**
     * 统计用户各状态报告数量
     */
    @Query("SELECT r.status, COUNT(r) FROM GeneratedReport r WHERE r.userId = :userId GROUP BY r.status")
    List<Object[]> countByUserIdGroupByStatus(@Param("userId") Long userId);

    /**
     * 获取用户最近的报告
     */
    List<GeneratedReport> findTop10ByUserIdOrderByCreatedAtDesc(Long userId);

    /**
     * 根据用户ID和ID列表批量查询
     */
    List<GeneratedReport> findByUserIdAndIdIn(Long userId, List<Long> ids);

    /**
     * 根据用户ID和ID列表批量删除
     */
    void deleteByUserIdAndIdIn(Long userId, List<Long> ids);

    /**
     * 获取用户指定时间范围内的报告统计
     */
    @Query("SELECT DATE(r.createdAt) as date, COUNT(r) as count " +
           "FROM GeneratedReport r " +
           "WHERE r.userId = :userId AND r.createdAt BETWEEN :startTime AND :endTime " +
           "GROUP BY DATE(r.createdAt) " +
           "ORDER BY date DESC")
    List<Object[]> getReportStatsByDateRange(@Param("userId") Long userId, 
                                           @Param("startTime") LocalDateTime startTime, 
                                           @Param("endTime") LocalDateTime endTime);

    /**
     * 获取用户报告创建趋势（按月统计）
     */
    @Query("SELECT YEAR(r.createdAt) as year, MONTH(r.createdAt) as month, COUNT(r) as count " +
           "FROM GeneratedReport r " +
           "WHERE r.userId = :userId " +
           "GROUP BY YEAR(r.createdAt), MONTH(r.createdAt) " +
           "ORDER BY year DESC, month DESC " +
           "LIMIT 12")
    List<Object[]> getReportTrendsByMonth(@Param("userId") Long userId);
}
